package com.zhiletu.collect.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

/**
 *
 * @Title: DBUtil.java
 * @Package com.zhiletu.collect.util
 * @Description: 数据库操作
 * @author zhiletu.com
 * @date 2021年3月11日
 * @version V1.0
 */
public class DBUtil {
	private static Logger log = Logger.getLogger(DBUtil.class);

	/**
	 * 无条件查询
	 * @param sql
	 * @param ip
	 * @param port
	 * @param dbname
	 * @param username
	 * @param passwd
	 * @return
	 */
	public static List<Map<String, Object>> querySql(final String sql, String ip, String port, String dbname, String username, String passwd) {
		final String url = "jdbc:mysql://" + ip + ":" + port + "/" + dbname + "?useUnicode=true&characterEncoding=UTF8";

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		java.sql.ResultSetMetaData rsmd = null;

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		try {

			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username, passwd);
			stmt = conn.createStatement();

			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				Map<String, Object> m = new HashMap<String, Object>();
				rsmd = rs.getMetaData();
				for (int x = 1; x <= rsmd.getColumnCount(); x++) {
					m.put(rsmd.getColumnName(x), rs.getObject(x));
				}
				list.add(m);
			}

		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}

		}

		return list;
	}

	/**
	 * 执行预编译查询
	 * @param sql
	 * @param parArray
	 * @param ip
	 * @param port
	 * @param dbname
	 * @param username
	 * @param passwd
	 * @return
	 */
	public static List<Map<String, Object>> querySql(final String sql, Object[] parArray, String ip, String port, String dbname, String username, String passwd) {
		final String url = "jdbc:mysql://" + ip + ":" + port + "/" + dbname + "?useUnicode=true&characterEncoding=UTF8";

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		java.sql.ResultSetMetaData rsmd = null;

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		try {

			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username, passwd);
			stmt = conn.prepareStatement(sql);
			ParameterMetaData pmd = stmt.getParameterMetaData();

			for (int i = 1; i <= pmd.getParameterCount(); i++) {
				stmt.setObject(i, parArray[i - 1]);
			}

			rs = stmt.executeQuery();//.executeQuery(sql);

			while (rs.next()) {
				Map<String, Object> m = new HashMap<String, Object>();
				rsmd = rs.getMetaData();
				for (int x = 1; x <= rsmd.getColumnCount(); x++) {
					m.put(rsmd.getColumnName(x), rs.getObject(x));
				}
				list.add(m);
			}

		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * 执行预编译查询
	 * @param sql
	 * @param parArray
	 * @param ip
	 * @param port
	 * @param dbname
	 * @param username
	 * @param passwd
	 * @return
	 */
	public static List<Map<String, Object>> querySql(final String sql, final Object[] parArray, final String url, final String username, final String passwd) {

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		java.sql.ResultSetMetaData rsmd = null;

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		try {

			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username, passwd);
			stmt = conn.prepareStatement(sql);
			ParameterMetaData pmd = stmt.getParameterMetaData();

			for (int i = 1; i <= pmd.getParameterCount(); i++) {
				stmt.setObject(i, parArray[i - 1]);
			}

			rs = stmt.executeQuery();

			while (rs.next()) {
				Map<String, Object> m = new HashMap<String, Object>();
				rsmd = rs.getMetaData();
				for (int x = 1; x <= rsmd.getColumnCount(); x++) {
					m.put(rsmd.getColumnName(x), rs.getObject(x));
				}
				list.add(m);
			}

		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * 执行查询
	 * @param sql
	 * @param username
	 * @param passwd
	 * @return
	 */
	public static List<Map<String, Object>> querySql(String sql, String url, String username, String passwd) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		java.sql.ResultSetMetaData rsmd = null;

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		try {

			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username, passwd);
			stmt = conn.createStatement();

			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				Map<String, Object> m = new HashMap<String, Object>();
				rsmd = rs.getMetaData();
				for (int x = 1; x <= rsmd.getColumnCount(); x++) {
					m.put(rsmd.getColumnName(x), rs.getObject(x));
				}
				list.add(m);
			}

		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/** 批量更新或新增*/
	public static int batchUpdate(String sql, List<Object[]> parArray, String ip, String port, String dbname, String username, String passwd) {
		final String url = "jdbc:mysql://" + ip + ":" + port + "/" + dbname + "?useUnicode=true&characterEncoding=UTF8";

		Connection conn = null;
		PreparedStatement stmt = null;
		int num = 0;
		try {

			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username, passwd);
			stmt = conn.prepareStatement(sql);
			ParameterMetaData pmd = stmt.getParameterMetaData();

			for (int x = 0; x < parArray.size(); x++) {
				Object[] data = parArray.get(x);

				for (int i = 1; i <= pmd.getParameterCount(); i++) {
					stmt.setObject(i, data[i - 1]);
				}
				// 添加进批处理
				stmt.addBatch();
			}

			int[] count = stmt.executeBatch();

			for (int i : count)
				num += i;// 影响多少列

		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				stmt.close();
				conn.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
			//System.out.println("成功更新："+num+"行");
		}

		return num;
	}

	/** 批量更新或新增*/
	public static int batchUpdate(final String sql, final List<Object[]> parArray, final String url, final String username, final String passwd) throws Exception {

		Connection conn = null;
		PreparedStatement stmt = null;
		int num = 0;
		try {

			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username, passwd);
			stmt = conn.prepareStatement(sql);
			ParameterMetaData pmd = stmt.getParameterMetaData();

			for (int x = 0; x < parArray.size(); x++) {
				Object[] data = parArray.get(x);

				for (int i = 1; i <= pmd.getParameterCount(); i++) {
					stmt.setObject(i, data[i - 1]);
				}
				// 添加进批处理
				stmt.addBatch();
			}

			int[] count = stmt.executeBatch();

			for (int i : count)
				num += i;// 影响多少列

		}
		catch (Exception e) {
			throw e;
		}
		finally {
			try {
				stmt.close();
				conn.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
			//log.info("成功更新："+num+"行");
		}

		return num;
	}

	/** 更新或新增一条记录*/
	public static int update(String sql, Object[] parArray, String url, String username, String passwd) {

		Connection conn = null;
		PreparedStatement stmt = null;
		int num = 0;
		try {

			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username, passwd);
			stmt = conn.prepareStatement(sql);
			ParameterMetaData pmd = stmt.getParameterMetaData();

			for (int i = 1; i <= pmd.getParameterCount(); i++) {
				stmt.setObject(i, parArray[i - 1]);
			}

			num = stmt.executeUpdate();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				stmt.close();
				conn.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
			//System.out.println("成功更新："+num+"行");
		}

		return num;
	}

	/** 更新或新增一条记录*/
	public static int update(String sql, Object[] parArray, String ip, String port, String dbname, String username, String passwd) {
		final String url = "jdbc:mysql://" + ip + ":" + port + "/" + dbname + "?useUnicode=true&characterEncoding=UTF8";

		Connection conn = null;
		PreparedStatement stmt = null;
		int num = 0;
		try {

			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username, passwd);
			stmt = conn.prepareStatement(sql);
			ParameterMetaData pmd = stmt.getParameterMetaData();

			for (int i = 1; i <= pmd.getParameterCount(); i++) {
				stmt.setObject(i, parArray[i - 1]);
			}

			num = stmt.executeUpdate();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				stmt.close();
				conn.close();
			}
			catch (SQLException e) {
				e.printStackTrace();
			}
			//System.out.println("成功更新："+num+"行");
		}

		return num;
	}

	/** 批量更新或新增
	 *  兼容事务处理
	 */
	public static int batchUpdate(final List<Object[]> parArray, PreparedStatement stmt) throws Exception {
		int num = 0;
		try {
			ParameterMetaData pmd = stmt.getParameterMetaData();

			for (int x = 0; x < parArray.size(); x++) {
				Object[] data = parArray.get(x);

				for (int i = 1; i <= pmd.getParameterCount(); i++) {
					stmt.setObject(i, data[i - 1]);
				}
				// 添加进批处理
				stmt.addBatch();
			}

			int[] count = stmt.executeBatch();

			for (int i : count)
				num += i;// 影响多少列

		}
		catch (Exception e) {
			throw e;
		}
		finally {
			//log.info("成功更新："+num+"行"); 
		}

		return num;
	}

	/** 更新或新增
	 *  兼容事务处理
	 */
	public static int update(Object[] params, PreparedStatement stmt) throws Exception {
		try {
			ParameterMetaData pmd = stmt.getParameterMetaData();

			for (int i = 1; i <= pmd.getParameterCount(); i++) {
				stmt.setObject(i, params[i - 1]);
			}

			stmt.executeUpdate();
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			//log.info("成功更新："+num+"行"); 
		}

		return 1;
	}


	public static Connection getConnection(String url, String username, String passwd) {
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, username, passwd);
		}
		catch (Exception e) {
			log.error(e);
		}
		return conn;
	}
}
